package business.knBase

import dao.Word
import groovy.sql.DataSet
import groovy.sql.Sql
import org.apache.log4j.Logger;
import java.util.regex.Pattern;
import business.database.*;

class CodigosExtranjeros {
	
	static main(args) {
	CodigosExtranjeros p = new CodigosExtranjeros();
	p.createForeignTable()
	p.fillForeignTable()
	}
	
	public void createForeignTable() {
		
		try {
		Database.getInstance().sqlKnBase.execute("create table IF NOT EXISTS foreignword (code varchar(4), word varchar(100));")
		Database.getInstance().sqlKnBase.execute "create index IF NOT EXISTS idx_frgn_word on foreignword(word);"
		} catch (Exception e) {
		
		}
		
	}
	
	/**
	 * Rellena la tabla de codigos extranjeros con las palabras que apuntan a ellos
	 */
	public void fillForeignTable() {
		Database.getInstance().startTransaction_Sqlite()
		
			String prevWord = "";
			
			Database.getInstance().sqlite.eachRow("select w.word, c.iso, c.country, count(*) as cnt from word w inner join groupedword gw on w.idword = gw.idword inner join corpgroup cg on cg.idgroup = gw.idgroup inner join " +
				"centers_corps cc on cc.idcorp = cg.idcorp inner join center c on c.idce = cc.idce where cg.type = -3 group by w.word, c.iso, c.country order by word,cnt desc" , {
				String word = it[0]
				println "${word}"
				String iso = it[1]
				if (!word.equals(prevWord)) {
					if ((word.size() > 2) && (!word.isNumber()) && (!generalWord(word)))			
						Database.getInstance().sqlKnBase.execute("INSERT INTO foreignword VALUES (${iso},${word})")
				}
				prevWord = word;
			})
			
	
		
		Database.getInstance().endTransaction_Sqlite()
	}
	
	
	boolean generalWord(String word) {
		//if (word.equalsIgnoreCase("Africa")) return true; no la quito que es la que me define South Africa
		if (word.equalsIgnoreCase("South")) return true;
		if (word.equalsIgnoreCase("America")) return true;
		if (word.equalsIgnoreCase("North")) return true;
		if (word.equalsIgnoreCase("New")) return true;
		if (word.equalsIgnoreCase("Republic")) return true;
		
		return false;
	}
	
	
	
}
